package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.AlarmPushDto;
import com.hualude.roadsign.dto.CornerDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.MonthCountDto;
import com.hualude.roadsign.utils.FormatNumber;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu_jiaHui on 2018/4/1.
 */
public class AlarmPushRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<AlarmPushDto> getAlarmPushHistory(Integer alarmType, String orgIds, String actors, Integer mCount, Integer mSize) throws Exception {

        String sql = "select alarmPushId,alarmPushContent,alarmPushTime,alarmPushToOrgIds,alarmPushToActorIds,alarmDeviceId from tb_alarm_push where alarmPushType=? " +
                "and alarmPushToOrgIds in (" + orgIds + ") and alarmPushToActorIds in (" + actors + ") ORDER BY alarmPushTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        query.setParameter(1, alarmType);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<AlarmPushDto> getAlarmTypeCount(String limitTime, String orgIds) throws Exception {
        String sql = "select count(*) as pushCount,AlarmPushType from tb_alarm_push WHERE unix_timestamp(AlarmPushTime) > unix_timestamp('" + limitTime + "') and AlarmPushToOrgIds=:orgIds   GROUP BY AlarmPushType";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        query.setParameter("orgIds", orgIds);
        return query.getResultList();
    }

    public CornerDto getCorner4App(Integer orgId, Integer deviceType, String thatTime) throws Exception {
        String sql = "select count(*) as pushCount,max(AlarmPushTime) as lastPushTime " +
                "from tb_alarm_push where AlarmPushToOrgIds=:orgId and AlarmPushTime> " +
                "(:thatTime) and AlarmPushType=:deviceType ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CornerDto.class));
        query.setParameter("orgId", orgId);
        query.setParameter("thatTime", thatTime);
        query.setParameter("deviceType", deviceType);
        CornerDto cornerDto = (CornerDto) query.getSingleResult();
        return cornerDto;
    }

    public List<AlarmPushDto> getCurrentPushList(Integer orgId, Integer deviceType, String newTime, String oldTime, Integer mCount, Integer mSize) throws Exception {
        String sql = "select alarmPushId,alarmPushContent,alarmPushTime,alarmPushToOrgIds,alarmPushToActorIds," +
                "alarmDeviceId,AlarmPushType from tb_alarm_push where alarmPushType=:deviceType and alarmPushToOrgIds=:orgId " +
                "and AlarmPushTime between (:oldTime) and (:newTime) order by AlarmPushTime desc ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("orgId", orgId);
        query.setParameter("oldTime", oldTime);
        query.setParameter("newTime", newTime);
        query.setParameter("deviceType", deviceType);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<AlarmPushDto> getAlarmHistory4DT(DTRequestDto dtRequestDto, String deviceId, Integer deviceType) throws Exception {
        String sql = "SELECT alarmPushId,alarmPushType,alarmPushContent,alarmPushTime,alarmPushToOrgIds,alarmPushToActorIds," +
                "alarmDeviceId from tb_alarm_push where alarmPushType=:alarmPushType and alarmDeviceId=:deviceId ";
        if (dtRequestDto.getSearch().length() > 10) {
            sql += " and  alarmPushTime between (:time1) and (:time2) ";
        }
        sql += "  order by alarmPushTime desc ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("alarmPushType", deviceType);
        query.setParameter("deviceId", deviceId);
        if (dtRequestDto.getSearch().length() > 10) {
            String[] time = dtRequestDto.getSearch().split(" - ");
            query.setParameter("time1", FormatNumber.transformDate(time[0]));
            query.setParameter("time2", FormatNumber.transformDate(time[1]));
        }
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAlarmHistoryCount4DT(DTRequestDto dtRequestDto, String deviceId, Integer deviceType) throws Exception {
        String sql = "SELECT count(*) from tb_alarm_push where alarmPushType=:alarmPushType and alarmDeviceId=:deviceId ";
        if (dtRequestDto.getSearch().length() > 10) {
            sql += " and  alarmPushTime between (:time1) and (:time2) ";
        }
        Query query = em.createNativeQuery(sql);
        query.setParameter("alarmPushType", deviceType);
        query.setParameter("deviceId", deviceId);
        if (dtRequestDto.getSearch().length() > 10) {
            String[] time = dtRequestDto.getSearch().split(" - ");
            query.setParameter("time1", FormatNumber.transformDate(time[0]));
            query.setParameter("time2", FormatNumber.transformDate(time[1]));
        }
        return Integer.parseInt(query.getSingleResult().toString());
    }


    public List<AlarmPushDto> getAlarmHistory4DT(DTRequestDto dtRequestDto, String deviceId, Integer deviceType, String orgIds, String subtime) throws Exception {
        boolean andFlag = false;
        StringBuilder sql = new StringBuilder().append("SELECT alarmPushId,alarmPushType,alarmPushContent,alarmPushTime,")
                .append("alarmPushToOrgIds,alarmPushToActorIds,alarmDeviceId,orgName from tb_alarm_push ")
                .append("left join tb_org o on o.orgId=alarmPushToOrgIds where  ");
        if (subtime != null && subtime.length() > 10) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushTime between (:time1) and (:time2) ");
        }
        if (deviceId != null && deviceId.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmDeviceId=:deviceId ");
        }
        if (deviceType != null && deviceType > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushType=:deviceType ");
        }
        if (orgIds != null && orgIds.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushToOrgIds in (").append(orgIds).append(") ");
        }
        sql.append("  order by alarmPushTime desc ");
        Query query = em.createNativeQuery(sql.toString());
        if (subtime != null && subtime.length() > 10) {
            String[] times = subtime.split(" - ");
            query.setParameter("time1", FormatNumber.transformDate(times[0]));
            query.setParameter("time2", FormatNumber.transformDate(times[1]));
        }
        if (deviceId != null && deviceId.length() > 0) {
            query.setParameter("deviceId", deviceId);
        }
        if (deviceType != null && deviceType > 0) {
            query.setParameter("deviceType", deviceType);
        }
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAlarmHistoryCount4DT(DTRequestDto dtRequestDto, String deviceId, Integer deviceType, String orgIds, String subtime) throws Exception {
        boolean andFlag = false;
        StringBuilder sql = new StringBuilder("SELECT count(*) from tb_alarm_push where ");
        if (subtime != null && subtime.length() > 10) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushTime between (:time1) and (:time2) ");
        }
        if (deviceId != null && deviceId.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmDeviceId=:deviceId ");
        }
        if (deviceType != null && deviceType > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushType=:deviceType ");
        }
        if (orgIds != null && orgIds.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushToOrgIds in (").append(orgIds).append(") ");
        }
        sql.append("  order by alarmPushTime desc ");
        Query query = em.createNativeQuery(sql.toString());
        if (subtime != null && subtime.length() > 10) {
            String[] times = subtime.split(" - ");
            query.setParameter("time1", FormatNumber.transformDate(times[0]));
            query.setParameter("time2", FormatNumber.transformDate(times[1]));
        }
        if (deviceId != null && deviceId.length() > 0) {
            query.setParameter("deviceId", deviceId);
        }
        if (deviceType != null && deviceType > 0) {
            query.setParameter("deviceType", deviceType);
        }
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<MonthCountDto> getMonthCount(String alarmPushType, String startTime, String endTime, String orgIds, String deviceId) throws Exception {
        boolean andFlag = false;
        StringBuilder sql = new StringBuilder("select count(*) as count,FROM_UNIXTIME(AlarmPushTime,'%Y%m') as month from tb_alarm_push where ");
        if (alarmPushType != null && alarmPushType.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushType=:alarmPushType ");
        }
        if (startTime != null && startTime.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" alarmPushTime between (:startTime) and (:endTime) ");
        }
        if (orgIds != null && orgIds.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" AlarmPushToOrgIds in (").append(orgIds).append(") ");
        }
        if (deviceId != null && deviceId.length() > 0) {
            if (!andFlag) {
                andFlag = true;
            } else {
                sql.append(" and ");
            }
            sql.append(" AlarmDeviceId = :deviceId ");
        }
        sql.append(" group by month ");
        Query query = em.createNativeQuery(sql.toString());
        if (alarmPushType != null && alarmPushType.length() > 0) {
            query.setParameter("alarmPushType", alarmPushType);
        }
        if (startTime != null && startTime.length() > 0) {
            query.setParameter("startTime", startTime);
            query.setParameter("endTime", endTime);
        }
        if (deviceId != null && deviceId.length() > 0) {
            query.setParameter("deviceId", deviceId);
        }
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonthCountDto.class));
        return query.getResultList();
    }

    public AlarmPushDto getLastAlarmPushByDeviceId(String deviceId)throws Exception{
        String sql="select AlarmPushId,AlarmPushType,AlarmPushContent,AlarmPushTime,AlarmPushToOrgIds,AlarmPushToActorIds," +
                "AlarmDeviceId from tb_alarm_push where AlarmDeviceId=:deviceId  order by alarmPushTime desc limit 1";

        Query query = em.createNativeQuery(sql);
        query.setParameter("deviceId", deviceId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmPushDto.class));
        return (AlarmPushDto) query.getSingleResult();
    }


}
